Spreadsheet programs are widely used general programs that run on personal computers and have become well known tools for manipulating information electronically. They are commonly used for budgets, forecasting, and other finance-related tasks.
A conventional electronic spreadsheet organises data in a two-dimensional array of rows and columns. Each intersection of a row and a column is termed a cell. The cell can store numbers and data strings, as well as formulas for computing a result based upon values contained in other cells. To ease computation, spreadsheet programs include built-in functions that perform standard calculations. A formula may be constructed within a cell of the spreadsheet using one or more of the built-in functions combined with any other constants or variables.
Some spreadsheet programs can also link one spreadsheet to another containing related information to the first. This operation allows data in linked spreadsheets to be updated automatically. Additionally, spreadsheet programs also include macro facilities. Macros automatically repeat tasks that are to be performed frequently. Furthermore, spreadsheets provide a very convenient and visual mechanism for programming array calculations, for example, formula wizards.
FIG. 1 is used to illustrate the method by which formulae in cells are computed in existing spreadsheets. The examples used to describe standard spreadsheets herein are based on Lotus 1-2-3. (Lotus 1-2-3 is a registered trademark of Lotus Development Corporation.) The results of computation are shown in cells E3 . . . G5 and the corresponding formulae are shown in FIG. 1a. 
On computation, the value 0 is held by cells E5 and G3 . . . G5, since the cells which are referenced, are empty. Existing spreadsheets assume the value of an empty cell is 0 and thus do not distinguish in normal calculations between a cell having null contents and one having contents set to 0. This problem is illustrated in FIG. 2, and a known method of detecting this problem in Lotus 1-2-3, for example, is to use an explicit @IsEmpty(location) check. This check tests the location for a blank cell and returns a 1 if the cell is empty and 0 if the cell is not.
To illustrate the need for improved error prompting, reporting and correction mechanisms, examples of the problems associated with existing spreadsheet technology are further described. The copy operation is used as an example. Referring to FIG. 2 a simplified known spreadsheet implementing a commission calculation for a single salesman is shown. With conventional cell naming, the formula in cell C2 is =B2*F2, in cell C3 is =B3*F3, and so on. Generally the calculation involves the formula Commission=Amount Sold*Rate.
In reality, the column of the table representing the “rate”, which would be common to all salespeople would be somewhat remote from the calculation. The calculation would be done for each salesperson. For example (not illustrated) the column containing “rate” values may be held on a different spreadsheet page.
When the spreadsheet of FIG. 2 is updated to include the values for a further month, “April” it is easy and quite common for an end user to copy down the calculation, but forgetting to extend the “rate” column. The copy down operation from C4=B4*F4 rewrites the formula for cell C5 as =B5*F5. However, since cell F5 is an empty cell, the value 0 is placed in C5.
As illustrated in FIG. 3, the errors obtained from an inaccurate copy operation can be more serious if the “commission” column is located on the same sheet together with constant values, for example the “VAT” value. When the spreadsheet is being updated for the month of “May”, to calculate the value for cell C6, the formula used is =B6*F6. However, since the constant value for the “VAT”, namely 17.5%, resides in cell FC6, it is this 0.175 value that is used in the equation =B6*F6.
The error obtained in FIG. 3 is more confusing to the end user as compared to FIG. 2. Cell C5 in FIG. 2 is easily distinguishable from the existing values in the column. However, in FIG. 3, the value obtained in cell C6 is not so easily differentiated and the problem becomes clear if the constant was set at a value of 17.5 instead of 17.5%, for example. In this case, the result in cell C6 would be 262.5 and because this is a similar value to the existing values in the column, the user may not be aware that an error has occurred. Also, this is not a problem that can be detected using the @IsEmpty function. Therefore, in existing spreadsheets the computation and display of a result still occurs even if the end user does not have knowledge of any associated errors with the result.
Thus, whereas detecting common syntax errors including placing multiple operators together and mismatching parentheses is conventional, the present invention seeks to highlight semantic errors and it is these errors that most often occur when a correct spreadsheet is updated without full awareness or thought to its structure. Currently, very little protection is provided against traditional “array bound” errors as described above, and programmers are therefore prone to write incorrect programs referencing meaningless empty cells, or worse, referencing cells with inappropriate meaning.
There are systems, such as CleanSheet that combine some of the benefits of spreadsheets with tighter programming controls. However, these systems rely too much on programming to be acceptable by typical spreadsheet programmers. Also, error detection and correction methods for formulae in a spreadsheet have been described in U.S. Pat. No. 5,842,180, issued to Karan Khanna et al. However, these systems provide no support for array bound errors and semantic errors.